﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace SqlHelper
{
    internal class SqlServer
    {
        public static string MySQLConn = "server=.;uid=sa;pwd=123456;database=BatteryDB;Trusted_Connection=no";//定义数据库连接参数
        //public static SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例 
        public static SqlCommand cmd = new SqlCommand();

        //更新表格信息
        public static int UpdateDB(string Table, string ID, string Field)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            //string MyUpdate = "Update T_WorkOrderInfo set OrderNumber='sab', ProductModel='3'  where WorkOrderID='10'";
            string MyUpdate = "Update " + Table + " set " + Field + " where " + ID;
            SqlCommand MyCommand = new SqlCommand(MyUpdate, MyConnection);
            int result;
            try
            {
                MyConnection.Open();
                result = MyCommand.ExecuteNonQuery();
                MyConnection.Close();

            }
            catch (Exception)
            {
                MyConnection.Close();

                result = 0;
            }
            return result;
        }
        //获取列名名称，需要自己写sql语句，可以查询多条
        public static List<string> GetSqlColumnName(string sql)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            List<string> columnName = new List<string>();
            MyConnection.Open();
            SqlCommand com = new SqlCommand(sql, MyConnection);
            SqlDataReader dr = com.ExecuteReader();
            //string sql = "select name from syscolumns where id = object_id('"+NameFile+"')";
            // string Sql = @"USE BatteryDB SELECT name FROM sys.sysobjects WHERE type='U' ORDER BY name";
            try
            {

                while (dr.Read())
                {
                    columnName.Add(dr[0].ToString());
                }
                dr.Close();
                MyConnection.Close();
            }
            catch (Exception)
            {
                dr.Close();
                MyConnection.Close();

            }

            return columnName;
        }
        //往指定表格插入数据
        public static int Insert_Message_into_DB(string Table, string Field, string Values)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            // string MyInsert = "insert into T_WorkOrderInfo (OrderNumber,ProductModel) values ('sa','1')";
            string MyInsert = "insert into " + Table + "(" + Field + ")" + "values" + "(" + Values + ")";
            SqlCommand MyCommand = new SqlCommand(MyInsert, MyConnection);
            int result;
            try//异常处理
            {
                MyConnection.Open();
                result = MyCommand.ExecuteNonQuery();
                MyConnection.Close();

            }
            catch (Exception)
            {
                MyConnection.Close();

                result = 0;
            }
            return result;
        }
        //获取列名
        public static List<string> GetSqlColumnNameByTableName(string TableName)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            List<string> columnName = new List<string>();
            string sql = "select column_name from information_schema.columns where  table_name='" + TableName + "' ";
            //string sql = "select name from syscolumns where id = object_id('"+NameFile+"')";
            // string Sql = @"USE BatteryDB SELECT name FROM sys.sysobjects WHERE type='U' ORDER BY name";
            MyConnection.Open();
            SqlCommand com = new SqlCommand(sql, MyConnection);
            SqlDataReader dr = com.ExecuteReader();
            try
            {
                while (dr.Read())
                {
                    columnName.Add(dr[0].ToString());
                }
                dr.Close();
                MyConnection.Close();
            }
            catch (Exception)
            {
                dr.Close();
                MyConnection.Close();

            }

            return columnName;
        }

        public static DataSet SelectDataDB(string MySelect)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            StringBuilder data = new StringBuilder();
            //查询单个数据
            SqlDataAdapter SelectAdapter = new SqlDataAdapter(MySelect, MyConnection);//定义一个数据适配器
            DataSet MyDS = new DataSet();
            try
            {
                SelectAdapter.Fill(MyDS);
                DataTable dt = MyDS.Tables[0];
                return MyDS;
            }

            catch (Exception)
            {
                //MessageBox.Show("运行" + MySelect + "：语句SelectDataDB函数报错:" + ex.Message, "cTraySN");

                return null;
            }

        }
        public static DataSet SelectTable(string TableName, string IDName)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            //选择表格TableName，以列名IDName的倒序排列
            string cmd = "SELECT TOP 100 * FROM " + TableName + " ORDER BY " + IDName + " DESC";
            StringBuilder Tabledate = new StringBuilder();
            DataSet MyDateSet = new DataSet();
            SqlCommand MyCommand = new SqlCommand(cmd, MyConnection);
            SqlDataAdapter SelectAdapter = new SqlDataAdapter
            {
                SelectCommand = MyCommand
            };
            SelectAdapter.Fill(MyDateSet);
            return MyDateSet;
        }


        public static DataSet get_message_from_table(string tableName)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = "select * from  " + tableName;
                ds = SelectDataDB(sql);
            }
            catch (Exception)
            {

            }
            return ds;
        }
        public static DataSet get_message_idTop_from_table(string tableName)
        {
            DataSet ds = new DataSet();
            try
            {

                string sql = "select TOP 1 * from " + tableName + " ORDER BY 'ID' DESC";
                //SELECT MaterialTypeID FROM InstallList where ID in (select max(ID) from InstallList)"
                ds = SelectDataDB(sql);
            }
            catch (Exception)
            {
                //cAuto.UpdateGUI((int)cAuto.UpdateItem.lstRcvMsg, "查询：" + tableName + "get_message_from_table报错:" + ex.Message + ":cTraySN");
                //cAuto.UpdateGUI((int)cAuto.UpdateItem.logList, "查询：" + tableName + "[get_message_from_table]报错:" + ex.Message + ":cTraySN");
            }
            return ds;
        }

        public static void update_message_to_table(string sql)
        {
            SqlConnection MyConnection = new SqlConnection(MySQLConn);//定义一个数据连接实例
            SqlCommand MyCommand = new SqlCommand(sql, MyConnection);
            //update 表名称 set 列名称 =新值 where 列名称 = 某值
            try
            {
                MyConnection.Open();
                MyCommand.ExecuteNonQuery();
                MyConnection.Close();
            }
            catch (Exception)
            {
                MyConnection.Close();

            }
        }

        //public static void write_message_into_InstallList(Dictionary<string, string> allMessage)
        //{
        //    try
        //    {
        //        //表格中有7项 ID,InstallListID,PlateID,MaterialTypeID,FacilityID,Account,ReadTag
        //        List<string> columnName = GetSqlColumnNameByTableName("InstallList");
        //        string columnNames = "";
        //        string values = "'" + allMessage["InstallListID"] + "','" + allMessage["MaterialID"] + "','" + allMessage["FacilityID"] + "','" + allMessage["Account"] + "','" + allMessage["ReadTag"] + "','" + allMessage["MaterialTypeID"] + "'";
        //        foreach (var item in columnName)
        //        {
        //            columnNames += item.ToString() + ",";
        //        }

        //        Insert_Message_into_DB("InstallList", columnNames.TrimEnd(',').Substring(3), values);
        //        //复位字典
        //        InstallList["InstallListID"] = "0";
        //        InstallList["PlateID"] = "0";
        //        InstallList["MaterialTypeID"] = "0";
        //        InstallList["FacilityID"] = "0";
        //        InstallList["Account"] = "0";
        //        InstallList["ReadTag"] = "0";

        //    }
        //    catch (Exception ex)
        //    {
        //        cAuto.UpdateGUI((int)cAuto.UpdateItem.logList, "[write_message_into_InstallList]写入失败:" + ex.ToString());
        //        return;
        //    }
        //}
    }
}
